package mysqlCnnection;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * This class controlls sales man roles
 * @author ameer
 * @author waleed
 * @author bshara
 * @author kamil
 */
public class SalesManController extends ConnectionTOmysql{
	
private Connection connection;
	
	/**
	 * Default constructor
	 * @throws IlegalMySQLPasswordORUserNameException 
	 */
	public SalesManController(){
		super();
		this.connection = super.getConnection();
	}
	
	/**
	 * This function imports all sales men from DB
	 * Return long[] sales men on success, null on fail
	 * @return  long[]
	 */
	public long[] loadAllSalesMen(){
		Statement stmt;
		Vector<Long> salesMenVect = new Vector<Long>();
		long[] salesMen;
		int i, len;
		String sqlmsg = ("SELECT eID FROM employees WHERE duty = 'SellsMen';");
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			
			while(rs.next()){
				salesMenVect.add(rs.getLong(1));					
			}				
			len = salesMenVect.size();
			salesMen = new long[len];
			for (i=0; i < len; i++){
				salesMen[i] = salesMenVect.get(i);				
			}	
			rs.close();
			return salesMen;
		} 
		catch (SQLException e){
			return null;
		}	
	}
	
	/**
	 * This function updates all sales men schedule
	 * Return true on success else false
	 * @return  boolean
	 */
	public boolean updateSalesMenSchedule(String schedule, long activeID){
		PreparedStatement ps = null;
		long salesMen, client;
		try {
			while (schedule.length() > 0){
				salesMen = Long.parseLong(schedule.substring(0, schedule.indexOf(":")));
				client = Long.parseLong(schedule.substring(schedule.indexOf(":")+1, schedule.indexOf(".")));
				schedule = schedule.substring(schedule.indexOf("\n")+1, schedule.length());							
				ps = (PreparedStatement) connection.prepareStatement("INSERT INTO sellsmenschedule VALUES (?,?,?);");
				ps.setLong(1, salesMen);
				ps.setLong(2, client);
				ps.setLong(3, activeID);				
				ps.executeUpdate();			
			}
			ps.close();	
			return true;
		} 
		catch (SQLException e){
				return false;
			}
	}
	
	/**
	 * This method load all client for sell man
	 * Return long[] of all client for sells mean.
	 * @return  long[]
	 */
	public long[] loadAllClientForSellsmen(String username){
		Statement stmt;
		Vector<Long> Vecsellsmen = new Vector<Long>();
		long[] sellsmenlist;
		int i, len;
		String sqlmsg = ("SELECT cID1 FROM sellsmenschedule AS S, employees AS E WHERE " +
				"E.eusername = '"+username+"' AND E.eID = S.eID"+";");
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			
			while(rs.next()){
				Vecsellsmen.add(rs.getLong(1));					
			}				
			len = Vecsellsmen.size();
			sellsmenlist = new long[len];
			for (i=0; i < len; i++){
				sellsmenlist[i] = Vecsellsmen.get(i);				
			}	
			rs.close();
			return sellsmenlist;
		} 
		catch (SQLException e){
			return null;
		}	
		
	}
	
	/**
	 * This method load all client for sell man
	 * Return long[] of all client for sells mean.
	 * @return  long[]
	 */
	public long loadDealSaleForClient(long cID){
		Statement stmt;
		String sqlmsg = ("SELECT ActiveID1 FROM sellsmenschedule WHERE " + "cID1 ="+cID+";");
		try{
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sqlmsg);
			if (rs.next()){
				return (rs.getLong(1));					
			}				
			rs.close();
			return 0;
		} 
		catch (SQLException e){
			return 0;
		}	
	}
	
	/**
	 * This method insert delay for Campaign
	 * Return true on success, false on error
	 * @return  boolean
	 */
	public boolean delayCampaign(long cID,long active , String message, Date date){
		PreparedStatement ps;
		try {
			ps = connection.prepareStatement("INSERT INTO delay VALUES (?,?,?,?);");
			ps.setLong(1, cID);
			ps.setLong(2, active);
			ps.setString(3, message);
			ps.setDate(4, date);
			ps.executeUpdate();
			ps = connection.prepareStatement("DELETE FROM sellsmenschedule WHERE cID1 = ?;");
			ps.setLong(1, cID);
			ps.executeUpdate();
			ps.close();
			return true;
		}	
		catch(SQLException e){
			return false;
		}
	}
	
	/**
	 * This method insert  purchases for Campaign
	 * Return true on success, false on error
	 * @return  boolean
	 */
	public boolean purchasesCampaign(long cID,long active, Date date){
		PreparedStatement ps;
		try {
			ps = connection.prepareStatement("INSERT INTO purchases VALUES (?,?,?);");
			ps.setLong(1, cID);
			ps.setLong(2, active);
			ps.setDate(3, date);
			ps.executeUpdate();
			ps = connection.prepareStatement("DELETE FROM sellsmenschedule WHERE cID1 = ?;");
			ps.setLong(1, cID);
			ps.executeUpdate();
			ps.close();
			return true;
		}	
		catch(SQLException e){
			return false;
		}
	}
}
